package com.server.test.database;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

/**
 * 基础DAO类
 * 
 * @author LYF
 */
public class BaseDao {
	private static boolean useUnicode=true;
	private static String characterEncoding="gb2312";
	/**
	 * 连接数据库的URL
	 */
//	private static String url="jdbc:mysql://127.0.0.1:3306/weixin";
	private static String url="jdbc:mysql://w.rdc.sae.sina.com.cn:3307/app_zhaoweiserver";
//	                           jdbc:mysql://r.rdc.sae.sina.com.cn:3307/app_myappname
	/**
	 * 登录数据库用户名
	 */
//	private static String user = "root";
	private static String user = "0zz125mmwl";
	/**
	 * 登录数据库密码
	 */
//	private static String password = "root";
	private static String password = "2yjzwk3wx3hzim0yhy3m5j1xlj100jhwxl4h4iz1";
	private static String driver = "com.mysql.jdbc.Driver";

	/**
	 * 所有线程共享的线程容器
	 */
	private static ThreadLocal<Map<String, Object>> threadLocal = null;

	// 静态块中实例化线程容器并装载数据库驱动
	static {
		init();
	}

	private static void init() {
		threadLocal = new ThreadLocal<Map<String, Object>>();
		try {
			Properties pro = new Properties();
//			pro.load(ClassLoader.getSystemResource("db.properties")
//					.openStream());
//			user = pro.getProperty("user");
//			password = pro.getProperty("password");
//			url = pro.getProperty("url");
//			System.out.println("user : " + url);
//			Class.forName(pro.getProperty("driver"));
			Class.forName(driver);
		 
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 获取连接对象Connection
	 * 
	 * @return
	 * @throws SQLException
	 */
	public Connection getConnection() throws SQLException {
		// 从线程中取出保存数据
		Map<String, Object> threadMap = threadLocal.get();
		if (null == threadMap) {
			threadMap = new HashMap<String, Object>();
			threadLocal.set(threadMap);
		}
		Connection conn = (Connection) threadMap.get("conn");
		if (null == conn || conn.isClosed()) {
			conn = DriverManager.getConnection(url, user, password);
			threadMap.put("conn", conn);
		}
		return conn;
	}

	/**
	 * 泛型方法(执行查询DQL)
	 * 
	 * @param <T>
	 * @param cla
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	public <T> List<T> executeQuery(Class<T> cla, String sql, Object... params)
			throws SQLException {
		// 获取连接对象
		Connection conn = this.getConnection();
		// 创建预编译语句对象
		PreparedStatement pstat = conn.prepareStatement(sql);
		// 将产生的语句对象放置到线程中
		PreparedStatement oldpstat = (PreparedStatement) threadLocal.get().put(
				"pstat", pstat);
		// 如果线程中有旧的语句对象则关闭它
		if (null != oldpstat)
			oldpstat.close();
		// 设置预编译占位符参数(参数从1开始)
		
		if (params != null) {
			for (int i = 0; i < params.length; i++) {
				pstat.setObject(i + 1, params[i]);
			}
		}

		List<T> list = new ArrayList<T>();
		// 执行查询返回结果集
		ResultSet res = pstat.executeQuery();
		// 将结果集放置到线程中
		ResultSet oldRes = (ResultSet) threadLocal.get().put("res", res);
		// 如果线程中有旧的结果集则关闭它
		if (null != oldRes)
			oldRes.close();

		// 获取结果集元数据
		ResultSetMetaData rsmd = res.getMetaData();
		// 返回结果集中字段数量(列的数量)
		int colNum = rsmd.getColumnCount();
		try {
			T t = null;
			while (res.next()) {// 遍历结果集中的所有记录
				t = cla.newInstance();// 每循环一次生成一个实体对象
				for (int i = 0; i < colNum; i++) {// 循环记录中的每个字段
					// 取出字段的名称
					String fieldName = rsmd.getColumnLabel(i + 1);
					
//					String fieldName=new String(rsmd.getColumnLabel(i + 1).getBytes("ISO-8859-1"), "GBK");
					
					
					
					// 取出字段的值
					Object object = res.getObject(fieldName);
					// 获取实体类的字段
					Field field = cla.getDeclaredField(fieldName);
					// 打开private权限的字段访问权限
					field.setAccessible(true);
					// 设置实体类对象字段属性值
					field.set(t, object);
				}
				list.add(t);// 将实体对象添加的返回列表中
			}
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (NoSuchFieldException e) {
			e.printStackTrace();
		} catch (SecurityException e) {
			e.printStackTrace();
		}  
		return list;
	}

	/**
	 * 执行增删改(DML)
	 * 
	 * @param sql
	 * @param params
	 * @return
	 */
	public int executeUpdate(String sql, Object... params) throws SQLException {
		Connection conn = this.getConnection();
		PreparedStatement pstat = conn.prepareStatement(sql);
		PreparedStatement oldpstat = (PreparedStatement) threadLocal.get().put(
				"pstat", pstat);
		if (null != oldpstat)
			oldpstat.close();
		for (int i = 0; i < params.length; i++) {
			pstat.setObject(i + 1, params[i]);
		}
		// 执行DML返回影响行数
		return pstat.executeUpdate();
	}

	/**
	 * 执行增加(insert)并且返回主键
	 * 
	 * @param sql
	 * @param params
	 * @return
	 */
	public int[] executeInsert(String sql, Object... params)
			throws SQLException {
		Connection conn = this.getConnection();
		PreparedStatement pstat = conn.prepareStatement(sql,
				PreparedStatement.RETURN_GENERATED_KEYS);
		PreparedStatement oldpstat = (PreparedStatement) threadLocal.get().put(
				"pstat", pstat);
		if (null != oldpstat)
			oldpstat.close();
		for (int i = 0; i < params.length; i++) {
			pstat.setObject(i + 1, params[i]);
		}
		// 返回影响行数(就是插入的记录数量)
		int count = pstat.executeUpdate();
		System.out.println(count);
		
		// 定义保存数据库生成的主键的数组
		int[] keys = new int[count];
		// 获取主键结果集
		ResultSet res = pstat.getGeneratedKeys();
		// 将结果集放置到当前线程中
		ResultSet oldRes = (ResultSet) threadLocal.get().put("res", res);
		if (null != oldRes)
			oldRes.close();
		// 遍历结果集,将结果集合中的所有主键取出来放到数组中
		for (int i = 0; res.next(); i++) {
			keys[i] = res.getInt(1);
		}
		return keys;
	}

	/**
	 * 关闭所有的连接(从线程中取出所有JDBC对象并关闭它们)
	 * 
	 * @throws SQLException
	 */
	public void closeAll() throws SQLException {
		Map<String, Object> map = threadLocal.get();
		Connection conn = (Connection) map.get("conn");
		PreparedStatement pstat = (PreparedStatement) map.get("pstat");
		ResultSet res = (ResultSet) map.get("res");
		if (null != res)
			res.close();
		if (null != pstat)
			pstat.close();
		if (null != conn)
			conn.close();
	}

}
